How to calling a store procedure with output parameters in ASP.Net c#?

In this article we will discuss, How to calling a store procedure with output parameters in ASP.Net c#. Write a store procedure with output parameter for employeeId with based on the employee table. The main thing is that to set EmployeeId marked as identity column.

We will be using employee table

Step 1: Create a table using the following script with data:

CREATE TABLE[dbo].[Employee](
      [EmployeeID] [int] IDENTITY(1,1) NOT NULL,
      [Name] [nvarchar](50) NULL,
      [Gender] [nvarchar](50) NULL,
      [City] [nvarchar](50) NULL,
 CONSTRAINT[PK_tbl_Employee] PRIMARY KEY CLUSTERED 
(
      [EmployeeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON[PRIMARY]
GO
SET IDENTITY_INSERT[dbo].[Employee] ON
INSERT [dbo].[Employee] ([EmployeeID], [Name], [Gender], [City]) VALUES (1, N'Thivan', N'male', N'tirunelveli')
INSERT [dbo].[Employee] ([EmployeeID], [Name], [Gender], [City]) VALUES (2, N'Rasik', N'male', N'Tuticorin')
INSERT [dbo].[Employee] ([EmployeeID], [Name], [Gender], [City]) VALUES (3, N'Usman', N'male', N'tirunelveli')
INSERT [dbo].[Employee] ([EmployeeID], [Name], [Gender], [City]) VALUES (4, N'karishma', N'female', N'mumbai')
INSERT [dbo].[Employee] ([EmployeeID], [Name], [Gender], [City]) VALUES (5, N'chaitrali', N'female ', N'mumbai')
INSERT [dbo].[Employee] ([EmployeeID], [Name], [Gender], [City]) VALUES (6, N'mansoor', N'male', N'gujarat')
INSERT [dbo].[Employee] ([EmployeeID], [Name], [Gender], [City]) VALUES (7, N'mydeen', N'male', N'chennai')
INSERT [dbo].[Employee] ([EmployeeID], [Name], [Gender], [City]) VALUES (8, N'zubair', N'male', N'melapalayam')
INSERT [dbo].[Employee] ([EmployeeID], [Name], [Gender], [City]) VALUES (9, N'matkar', N'male', N'mumbai')
INSERT [dbo].[Employee] ([EmployeeID], [Name], [Gender], [City]) VALUES (10, N'Rahim', N'male', N'mumbai')
SET IDENTITY_INSERT[dbo].[Employee] OFF

Store procedure:

CREATE PROCEDUREusp_InsertEmployee
@Namenvarchar(50),  
@Gendernvarchar(20),  
@Citynvarchar(50),  
@EmployeeIdint Out  
AS 
BEGIN 
 INSERT INTO Employee values(@Name, @Gender, @City)  
 SELECT@EmployeeId = SCOPE_IDENTITY()  
END

Step 2: Copy and paste the following code.

Default.aspx:

<table style="border: 1px solid #e2e2e2; font-family: Arial">
        <tr>
            <td>Employee Name
            </td>
            <td>
                <asp:TextBox ID="txtEmployeeName" runat="server"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td>Gender
            </td>
            <td>
                <asp:DropDownList ID="ddlGender" runat="server">
                   <asp:ListItem>Male</asp:ListItem>
                   <asp:ListItem>Female</asp:ListItem>
                </asp:DropDownList>
            </td>
        </tr>
        <tr>
            <td>City
            </td>
            <td>
               <asp:TextBox ID="txtCity" runat="server"></asp:TextBox>
           </td>
        </tr>
        <tr>
            <td colspan="2">
                <asp:Button ID="btnSubmit" runat="server" Text="Submit"
                   OnClick="btnSubmit_Click" />
            </td>
        </tr>
        <tr>
            <td colspan="2">
                <asp:Label ID="lblMessage" runat="server"></asp:Label>
            </td>
        </tr>
    </table>

Default.aspx.cs:

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
 
public partial class _Default : Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
    }
    protected void btnSubmit_Click(object sender, EventArgs e)
    {
        //Read the connection string fromWeb.Config file
        string ConnectionString = ConfigurationManager.ConnectionStrings["ShoppingZone"].ConnectionString;
        using (SqlConnection con = new SqlConnection(ConnectionString))
        {
            //Create the SqlCommand object
            SqlCommand cmd = new SqlCommand("usp_InsertEmployee", con);
            //Specify that the SqlCommand is astored procedure
           cmd.CommandType = System.Data.CommandType.StoredProcedure;
            //Add the input parameters to thecommand object
           cmd.Parameters.AddWithValue("@Name", txtEmployeeName.Text);
           cmd.Parameters.AddWithValue("@Gender", ddlGender.SelectedValue);
           cmd.Parameters.AddWithValue("@City", txtCity.Text);
            //Add the output parameter to thecommand object
            SqlParameter outPutParameter = new SqlParameter();
           outPutParameter.ParameterName = "@EmployeeId";
           outPutParameter.SqlDbType = System.Data.SqlDbType.Int;
           outPutParameter.Direction = System.Data.ParameterDirection.Output;
           cmd.Parameters.Add(outPutParameter);
            //Open the connection and executethe query
           con.Open();
           cmd.ExecuteNonQuery();
          //Retrieve the value of the outputparameter
           string EmployeeId =outPutParameter.Value.ToString();
           lblMessage.Text = "EmployeeId = " + EmployeeId;
        }
    }
} 

Output: